package soccer.model.leagueTable;

import java.util.List;

import soccer.utility.db.dbUtility;


public class LeagueTableDAOImpl implements LeagueTableDAO {
         public void  enterLeagueTable(LeagueTable lgt){
        	 StringBuffer query = new StringBuffer();
        	 query.append("INSERT INTO ");
        	 query.append("(week,playedMatchNumber,average,wonMatchedNumber,drawMatchedNumber,defeatedMatchedNumber,teamId) ");
        	 query.append(" VALUES(?,?,?,?,?,?,?)");
           
        	 Object[] params = new Object[]{lgt.getWeek(),
        			                        lgt.getPlayedMatchNumber(),
        			                        lgt.getAverage(),
        			                        lgt.getWonMatchNumber(),
        			                        lgt.getDrawMatchNumber(),
        			                        lgt.getDefeatedMatchNumber(),
        			                        lgt.getTeamId()
        	                                };
        	 dbUtility db = null;
        	 db = db.getInstance();
        	 db.update(query.toString(),params);
         }
              public List getLeagueTablesByWeek(int week){
               StringBuffer query = new StringBuffer();     
               query.append("SELECT * ,(3*l.wonMatchNumber + l.drawMatchNumber) AS point1 FROM leagueTable l , team t ");
               query.append(" WHERE week = ? AND l.teamId = t.teamKodu");
               query.append("  ORDER BY (3*l.wonMatchNumber + l.drawMatchNumber)");
               
               Object[] params = new Object[]{week};
               
               dbUtility db = null;
               db = db.getInstance();
               List leagueTableList = null;
               leagueTableList = db.execute(query.toString(),params);
               return leagueTableList;
         }
         public List getLeagueTablesByWeekAndTeam(int week ,int teamId){
        	 StringBuffer query = new StringBuffer();
        	 query.append("SELECT * FROM leagueTable ");
        	 query.append(" WHERE week = ? and teamId = ?");
        	 
        	 Object [] params = new Object[]{week , teamId};
        	  dbUtility db = null;
        	  db = db.getInstance();
        	  List leagueTableList = null;
        	  leagueTableList = db.execute(query.toString(), params);
        	  return leagueTableList;
         }
         public List getLeagueTablesByTeam(int teamId){
        	 StringBuffer query = new StringBuffer();     
             query.append("SELECT * FROM leagueTable ");
             query.append(" WHERE teamId = ");
             query.append(teamId);
             
             dbUtility db = null;
             db = db.getInstance();
             List leagueTableList = null;
             leagueTableList = db.execute(query.toString());
             return leagueTableList;
         }
         public List getWinNumberOfTeams(int week){
        	 StringBuffer query = new StringBuffer();
        	 query.append("SELECT t.teamName , SUM(l.wonMatchNumber) AS win FROM leagueTable l , team t ");
        	 query.append(" WHERE l.teamId = t.teamKodu and week = ?");
        	 query.append(" GROUP BY l.teamId ORDER BY  win DESC");
        	 
        	 Object[] params = new Object[]{week};
        	 dbUtility db = dbUtility.getInstance();
        	 List list = db.execute(query.toString(), params);
        	 return list;
         }
         public List getLostNumberOfTeams(int week){
        	 StringBuffer query = new StringBuffer();
        	 query.append("SELECT t.teamName , SUM(l.defeatedMatchNumber) AS lost FROM leagueTable l , team t ");
        	 query.append(" WHERE l.teamId = t.teamKodu and week = ?");
        	 query.append(" GROUP BY l.teamId ORDER BY  lost DESC");
        	 
        	 Object[] params = new Object[]{week};
        	 dbUtility db = dbUtility.getInstance();
        	 List list = db.execute(query.toString(), params);
        	 return list;
         }
         public List getMostGoalsNumberOfTeams(int week){
        	 StringBuffer query = new StringBuffer();
        	 query.append("SELECT t.teamName , SUM(l.attigiGol) AS attigi FROM leagueTable l , team t ");
        	 query.append(" WHERE l.teamId = t.teamKodu and week = ?");
        	 query.append(" GROUP BY l.teamId ORDER BY  attigi DESC");
        	 
        	 Object[] params = new Object[]{week};
        	 dbUtility db = dbUtility.getInstance();
        	 List list = db.execute(query.toString(), params);
        	 return list;
         }
         public List getMostGoalAgainstNumberOfTeams(int week){
        	 StringBuffer query = new StringBuffer();
        	 query.append("SELECT t.teamName , SUM(l.yedigiGol) AS yedigi FROM leagueTable l , team t ");
        	 query.append(" WHERE l.teamId = t.teamKodu and week = ?");
        	 query.append(" GROUP BY l.teamId ORDER BY  yedigi DESC");
        	 
        	 Object[] params = new Object[]{week};
        	 dbUtility db = dbUtility.getInstance();
        	 List list = db.execute(query.toString(), params);
        	 return list;
         }
         public List getWeekNumberOfLeagueTable(){
        	 StringBuffer query = new StringBuffer();
        	 query.append("SELECT COUNT(week) FROM leagueTable GROUP BY week " );
        	 dbUtility db = dbUtility.getInstance();
        	 List list = db.execute(query.toString());
        	 return list;
         }
         
}
